import web.data.DataUtil as dbutil
import web.entity.Column as columnEntity

def selectNotislable():
    clist = list()
    rows = dbutil.selectSqllite("select id,title,pid,islabel,sort,t1.coun from pi_column left join (select cid,count(aid) coun from pi_ca group by cid) as t1 on pi_column.id = t1.cid where pi_column.islabel = 0 order by pi_column.sort")
    for r in rows:
        colnow = columnEntity.Column(r[0], r[1], r[2], r[3], r[4], r[5], '')
        clist.append(colnow)
    return clist


def selectislable():
    clist = list()
    rows = dbutil.selectSqllite("select id,title,pid,islabel,sort,t1.coun from pi_column left join (select cid,count(aid) coun from pi_ca group by cid) as t1 on pi_column.id = t1.cid where pi_column.islabel = 1 order by pi_column.sort")
    for r in rows:
        colnow = columnEntity.Column(r[0], r[1], r[2], r[3], r[4], r[5], '')
        clist.append(colnow)
    return clist


def creatColumn(title, islabel, sort):
    sql = "insert into pi_column(`title`,`islabel`,`sort`) values('%s','%s','%s')" % (title, islabel, sort)
    dbutil.createSqllite(sql)


def deletColumnById(id):
    sql1 = "delete from pi_column where id = %s" % id
    sql2 ="delete from pi_ca where cid = %s" % id
    dbutil.deleteSqllite(sql1)
    dbutil.deleteSqllite(sql2)


def updatColumn(id, title, islabel, sort):
    sql = "update pi_column set `title` = '%s',`islabel`= '%s',`sort` = '%s' where id = '%s'" % (title, islabel, sort, id)
    dbutil.updateSqllite(sql)


def selectMoveColumn(aid):
    clist = list()
    sql = "SELECT pi_column.id, pi_column.title, ifnull( tab.coun, 0 ) AS 'checked' FROM pi_column LEFT JOIN ( SELECT cid,count(aid) as coun FROM pi_ca WHERE aid = '%s' group by cid ) AS tab ON pi_column.id = tab.cid where pi_column.id != 0 ORDER BY pi_column.islabel asc,pi_column.sort" % aid
    rows = dbutil.selectSqllite(sql)
    for r in rows:
        colnow = columnEntity.Column(r[0], r[1], '', '', '', '', r[2])
        clist.append(colnow)
    return clist


def moveColumnSave(aid, cids):
    delsql = "delete from pi_ca where aid = %s " % aid
    dbutil.deleteSqllite(delsql)
    for c in cids:
        sql = "insert into pi_ca(`cid`,`aid`) values('%s','%s')" % (c, aid)
        dbutil.createSqllite(sql)

def selectColumnById(id):
    sql = "select id,title,pid,islabel,sort from pi_column where id = '%s'" % id
    r = dbutil.selectOneSqllite(sql)
    if r != 0:
        return columnEntity.Column(r[0], r[1], r[2], r[3], r[4], '', '')
    else:
        return columnEntity.Column(0, '空栏目，请使用0查询所有', '', '', '', '', '')